ALTER VIEW [dbo].[vw_FG_Wip_View]
AS
SELECT     IL.Expected_Date_Out, LT.ProcessStep_ID, L.L_Vendor_Name, LT.Lot_Number, LT.Fab_Lot_Number, ISNULL(LT.Lot_Category, 'PROD') 
                      AS LOT_CATEGORY, ISNULL(IL.IL_Qty_Balance, IL.IL_Qty_In) AS QTY_IN, LT.Date_In, LT.Lot_Current_Status, NULL AS LABLE_NAME, P.Part_Number,
                          (SELECT     Part_Number
                            FROM          dbo.Product
                            WHERE      (Product_ID = CASE WHEN (P.P_PARENT_PRODUCT_ID IS NULL OR
                                                   P.P_PARENT_PRODUCT_ID = - 1) THEN P.PRODUCT_ID WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL 
                                                   THEN P.P_PARENT_PRODUCT_ID END)) AS BASE_PRODUCT, (CASE WHEN (P.P_PARENT_PRODUCT_ID IS NULL OR
                      P.P_PARENT_PRODUCT_ID = - 1) THEN P.PRODUCT_ID WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID END) 
                      AS BASE_PRODUCT_ID, PS.PS_Type_Name, PS.PS_Type, 9 AS PS_TYPE_ID, LT.Lot_ID, LT.Location_ID, NULL AS PACKAGE_TYPE
FROM         dbo.Inventory_Lot AS IL INNER JOIN
                      dbo.Lot_Table AS LT ON IL.Lot_ID = LT.Lot_ID INNER JOIN
                      dbo.Product AS P ON LT.ProcessStep_ID = P.ProcessStep_ID INNER JOIN
                      dbo.Process_Step AS PS ON P.ProcessStep_ID = PS.ProcessStep_ID INNER JOIN
                      dbo.Location AS L ON LT.Location_ID = L.Location_ID
WHERE     (ISNULL(IL.IL_Qty_Balance, IL.IL_Qty_In) > 0) AND (LT.Lot_Current_Status = 'Running' OR
                      LT.Lot_Current_Status = 'Hold') AND (P.Obsolete <> 0) AND (PS.PS_Type_Name = 'FINISHED GOODS')

